{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Guest House - Easy\n",
    "\n",
    "Data for this assessment is available:\n",
    "\n",
    "- [guesthouse data in MySQL format](http://sqlzoo.net/guesthouse.sql)\n",
    "- [guesthouse data in Microsoft SQL Server format](http://sqlzoo.net/guesthouse-ms.sql)\n",
    "\n",
    "Background\n",
    "\n",
    "- Guests stay at a small hotel.\n",
    "- Each booking is recorded in the table booking, the date of the first night of the booking is stored here (we do not record the date the booking was made)\n",
    "- At the time of booking the room to be used is decided\n",
    "- There are several room types (single, double..)\n",
    "- The amount charged depends on the room type and the number of people staying and the number of nights\n",
    "- Guests may be charged extras (for breakfast or using the minibar)\n",
    "- **Database Description** | [Easy Problems](https://sqlzoo.net/wiki/Guest_House_Assessment_Easy) | [Medium Problems](https://sqlzoo.net/wiki/Guest_House_Assessment_Medium) | [Hard Problems](https://sqlzoo.net/wiki/Guest_House_Assessment_Hard)\n",
    "- [Guest House Assessment Sample Queries](https://sqlzoo.net/wiki/Guest_House_Assessment_Sample_Queries)\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/8/83/Hotel.png)\n",
    "\n",
    "## Table booking\n",
    "The table booking contains an entry for every booking made at the hotel. A booking is made by one guest - even though more than one person may be staying we do not record the details of other guests in the same room. In normal operation the table includes both past and future bookings.\n",
    "\n",
    "```\n",
    "+----------+------------+-------+--------+---------+-------------------+------+------------+\n",
    "|booking_id|booking_date|room_no|guest_id|occupants|room_type_requested|nights|arrival_time|\n",
    "+----------+------------+-------+--------+---------+-------------------+------+------------+\n",
    "|     5001 | 2016-11-03 |   101 |   1027 |       1 | single            |    7 | 13:00      |\n",
    "|     5002 | 2016-11-03 |   102 |   1179 |       1 | double            |    2 | 18:00      |\n",
    "|     5003 | 2016-11-03 |   103 |   1106 |       2 | double            |    2 | 21:00      |\n",
    "|     5004 | 2016-11-03 |   104 |   1238 |       1 | double            |    3 | 22:00      |\n",
    "+----------+------------+-------+--------+---------+-------------------+------+------------+\n",
    "```\n",
    "\n",
    "## Table room\n",
    "Rooms are either single, double, twin or family.\n",
    "\n",
    "```\n",
    "+-----+-----------+---------------+\n",
    "| id  | room_type | max_occupancy |\n",
    "+-----+-----------+---------------+\n",
    "| 101 | single    |             1 |\n",
    "| 102 | double    |             2 |\n",
    "| 103 | double    |             2 |\n",
    "| 104 | double    |             2 |\n",
    "| 105 | family    |             3 |\n",
    "+-----+-----------+---------------+\n",
    "```\n",
    "\n",
    "## Table rate\n",
    "Rooms are charged per night, the amount charged depends on the \"room type requested\" value of the booking and the number of people staying:\n",
    "\n",
    "```\n",
    "+-----------+-----------+--------+\n",
    "| room_type | occupancy | amount |\n",
    "+-----------+-----------+--------+\n",
    "| double    |         1 |  56.00 |\n",
    "| double    |         2 |  72.00 |\n",
    "| family    |         1 |  56.00 |\n",
    "| family    |         2 |  72.00 |\n",
    "| family    |         3 |  84.00 |\n",
    "| single    |         1 |  48.00 |\n",
    "| twin      |         1 |  50.00 |\n",
    "| twin      |         2 |  72.00 |\n",
    "+-----------+-----------+--------+\n",
    "```\n",
    "\n",
    "You can see that a double room with one person staying costs £56 while a double room with 2 people staying costs £72 per night\n",
    "\n",
    "Note that the actual room assigned to the booking might not match the room required (a customer may ask for a single room but we actually assign her a double). In this case we charge at the \"requirement rate\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "Guest 1183. Give the booking_date and the number of nights for guest 1183.\n",
    "\n",
    "```\n",
    "+--------------+--------+\n",
    "| booking_date | nights |\n",
    "+--------------+--------+\n",
    "| 2016-11-27   |      5 |\n",
    "+--------------+--------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>booking_date</th>\n",
       "        <th>nights</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-27</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2016-11-27', 5)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT booking_date, nights FROM booking\n",
    "  WHERE guest_id=1183"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "When do they get here? List the arrival time and the first and last names for all guests due to arrive on 2016-11-05, order the output by time of arrival.\n",
    "\n",
    "```\n",
    "+--------------+------------+-----------+\n",
    "| arrival_time | first_name | last_name |\n",
    "+--------------+------------+-----------+\n",
    "| 14:00        | Lisa       | Nandy     |\n",
    "| 15:00        | Jack       | Dromey    |\n",
    "| 16:00        | Mr Andrew  | Tyrie     |\n",
    "| 21:00        | James      | Heappey   |\n",
    "| 22:00        | Justin     | Tomlinson |\n",
    "+--------------+------------+-----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>arrival_time</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14:00</td>\n",
       "        <td>Lisa</td>\n",
       "        <td>Nandy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15:00</td>\n",
       "        <td>Jack</td>\n",
       "        <td>Dromey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16:00</td>\n",
       "        <td>Mr Andrew</td>\n",
       "        <td>Tyrie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21:00</td>\n",
       "        <td>James</td>\n",
       "        <td>Heappey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22:00</td>\n",
       "        <td>Justin</td>\n",
       "        <td>Tomlinson</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('14:00', 'Lisa', 'Nandy'),\n",
       " ('15:00', 'Jack', 'Dromey'),\n",
       " ('16:00', 'Mr Andrew', 'Tyrie'),\n",
       " ('21:00', 'James', 'Heappey'),\n",
       " ('22:00', 'Justin', 'Tomlinson')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT arrival_time, first_name, last_name\n",
    "FROM booking JOIN guest ON (booking.guest_id=guest.id)\n",
    "WHERE booking_date='2016-11-05'\n",
    "ORDER BY arrival_time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "Look up daily rates. Give the daily rate that should be paid for bookings with ids 5152, 5165, 5154 and 5295. Include booking id, room type, number of occupants and the amount.\n",
    "\n",
    "```\n",
    "+------------+---------------------+-----------+--------+\n",
    "| booking_id | room_type_requested | occupants | amount |\n",
    "+------------+---------------------+-----------+--------+\n",
    "|       5152 | double              |         2 |  72.00 |\n",
    "|       5154 | double              |         1 |  56.00 |\n",
    "|       5295 | family              |         3 |  84.00 |\n",
    "+------------+---------------------+-----------+--------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>booking_id</th>\n",
       "        <th>room_type_requested</th>\n",
       "        <th>occupants</th>\n",
       "        <th>amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5152</td>\n",
       "        <td>double</td>\n",
       "        <td>2</td>\n",
       "        <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5154</td>\n",
       "        <td>double</td>\n",
       "        <td>1</td>\n",
       "        <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5295</td>\n",
       "        <td>family</td>\n",
       "        <td>3</td>\n",
       "        <td>84.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5152, 'double', 2, 72.0),\n",
       " (5154, 'double', 1, 56.0),\n",
       " (5295, 'family', 3, 84.0)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT booking.booking_id, room_type_requested, occupants, rate.amount\n",
    "  FROM booking INNER JOIN room ON (booking.room_no=room.id)\n",
    "    INNER JOIN rate ON (room.room_type=rate.room_type AND \n",
    "                        booking.occupants=rate.occupancy)\n",
    "    WHERE booking.booking_id IN (5152,6165,5154,5295)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "Who’s in 101? Find who is staying in room 101 on 2016-12-03, include first name, last name and address.\n",
    "\n",
    "```\n",
    "+------------+-----------+-------------+\n",
    "| first_name | last_name | address     |\n",
    "+------------+-----------+-------------+\n",
    "| Graham     | Evans     | Weaver Vale |\n",
    "+------------+-----------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Graham</td>\n",
       "        <td>Evans</td>\n",
       "        <td>Weaver Vale</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Graham', 'Evans', 'Weaver Vale')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT first_name, last_name, address\n",
    "FROM booking JOIN guest ON booking.guest_id=guest.id\n",
    "WHERE room_no=101 AND \n",
    "    booking_date BETWEEN '2016-12-03' AND DATE_ADD('2016-12-03', nights)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "How many bookings, how many nights? For guests 1185 and 1270 show the number of bookings made and the total number of nights. Your output should include the guest id and the total number of bookings and the total number of nights.\n",
    "\n",
    "```\n",
    "+----------+---------------+-------------+\n",
    "| guest_id | COUNT(nights) | SUM(nights) |\n",
    "+----------+---------------+-------------+\n",
    "|     1185 |             3 |           8 |\n",
    "|     1270 |             2 |           3 |\n",
    "+----------+---------------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>guest_id</th>\n",
       "        <th>count_nights</th>\n",
       "        <th>sum_nights</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1185</td>\n",
       "        <td>3</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1270</td>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1185, 3, 8), (1270, 2, 3)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT guest_id, COUNT(nights) count_nights, SUM(nights) sum_nights\n",
    "FROM booking\n",
    "WHERE guest_id IN (1185, 1270)\n",
    "GROUP BY guest_id"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
